# coding: UTF-8
# Filename : stat_goods.py
# Company : imedia
# Author : rose
# -*-coding:utf-8 -*-
'对应goods.php文件逻辑  挖掘入库stat_goods'

import sys
import utils, log, mysql

#日期设定
yesterday = str(utils.getYesterday())
if len(sys.argv) >= 2:
    yesterday = sys.argv[1]
    
print yesterday

del_sql = '''delete from stat_goods_show where `date` = %s'''

ok = mysql.delete(del_sql, (yesterday))

search_sql = '''select cus_id, p_id, count(p_id) as luvs from im_log_s 
where `time` >= %s
and `time` <= %s
group by p_id, cus_id
'''

start_time_str = yesterday + ' 00:00:00'
end_time_str = yesterday + ' 23:59:59'
format = '%Y-%m-%d %H:%M:%S'
start_time = utils.strToTimestamp(start_time_str, format)
end_time = utils.strToTimestamp(end_time_str, format)

#
dict_list_uv = {}
rst = mysql.doSearch(search_sql, [start_time, end_time])
#`cus_id`,`product_id`, `list_uv`入库逻辑
insert_sql = '''insert  into `stat_goods_show` 
 (`cus_id`,`product_id`,`date`, `list_uv`) 
 values (%s, %s, %s, %s)'''
for obj in rst:
    dict_list_uv[obj[1]] = obj[2]
    param = (obj[0], obj[1], yesterday, obj[2])
    mysql.insert(insert_sql, param)

#查询详细展示uv
search_detail_uv_sql = '''select cus_id, p_id, count(distinct(ip)) as detail_uv from im_log 
where `time` >= %s
and `time` <= %s
and type = 1
group by p_id
'''
#插入展示uv 和展示率
update_detail_uv_sql = '''update stat_goods_show set detail_uv = %s, goods_rate = %s
 where cus_id = %s
 and product_id = %s
 and `date` = %s'''

#展示uv 与展示率挖掘
dict_list_detail_uv = {}
rst = mysql.doSearch(search_detail_uv_sql, [start_time, end_time])
for obj in rst:
    dict_list_detail_uv[obj[1]] = obj[2]
    if dict_list_uv.has_key(obj[1]):
        list_uv = dict_list_uv[obj[1]]
        detail_uv = obj[2]
        goods_rate =  float(detail_uv)/float(list_uv) * 100
        #格式化数据
        goods_rate = "%.2f" % goods_rate
        mysql.update(update_detail_uv_sql, [detail_uv, str(goods_rate), obj[0], obj[1], yesterday])

#点击购买 挖掘
search_buy_click_sql = '''select cus_id, p_id, count(ip) as buy_count from im_log 
where `time` >= %s
and `time` <= %s
and  `type` in (2, 88)
group by p_id
'''
#插入点击购买
update_buy_click_sql = '''update stat_goods_show set buy_click_count = %s
 where cus_id = %s
 and product_id = %s
 and `date` = %s'''
rst = mysql.doSearch(search_buy_click_sql, [start_time, end_time])
for obj in rst :
    if dict_list_uv.has_key(obj[1]):
        #detail_uv = dict_list_detail_uv[obj[1]]
        buy_click = obj[2]
        #order_rate =  float(buy_click)/float(detail_uv) * 100
        #格式化数据
        #order_rate = "%.2f" % order_rate
        mysql.update(update_buy_click_sql, [buy_click, obj[0], obj[1], yesterday])
   
#下单量 订单转化率挖掘
search_order_count_sql = '''
select sum(p_count) as ots from order_product 
where  active='1'
and product_id = %s
'''
search_pid_sql = '''
select product_id from stat_goods_show
where `date` = %s
'''
#插入下单量 订单转化率
update_order_count_sql = '''update stat_goods_show 
set order_count = %s,
order_rate = %s
where product_id = %s
and `date` = %s'''
rst = mysql.doSearch(search_pid_sql, [yesterday])
for obj in rst:
    p_id = obj[0]
    p_count_obj = mysql.get(search_order_count_sql, [p_id])
    p_count = p_count_obj[0]
    if p_count == None: p_count = 0
    if dict_list_detail_uv.has_key(p_id):
        detail_uv = dict_list_detail_uv[p_id]
        order_rate =  float(p_count)/float(detail_uv) * 100
        #格式化数据
        order_rate = "%.2f" % order_rate
        mysql.update(update_order_count_sql, [p_count, str(order_rate), p_id, yesterday])

#销售金额 挖掘
search_sales_sql = '''select sum(p_count * price) all_price from order_product where product_id = %s'''
#插入销售金额
update_order_count_sql = '''update stat_goods_show 
set sales = %s 
where product_id = %s
and `date` = %s'''
rst = mysql.doSearch(search_pid_sql, [yesterday])
for obj in rst:
    p_id = obj[0]
    sales_obj = mysql.get(search_sales_sql, [p_id])
    if sales_obj == None: sales = 0
    else: sales = sales_obj[0]
    if sales == None: sales = 0
    mysql.update(update_order_count_sql, [sales, p_id, yesterday])

log.info('stat_goods_show ' + yesterday + ' 数据挖掘成功')
